package signHelper.dao;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import signHelper.modle.Semester;

@Repository
public class SemesterDao {
	private static final RowMapper<Semester> rm = JdbcHelper.getRowMapper(Semester.class);
	private @Autowired JdbcTemplate jdbcTemplate;
	private @Autowired NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	public Semester add(Semester param) {
		param.setId(JdbcHelper.insertAndGetKey(namedParameterJdbcTemplate,Sql.ADD, param));
		return param;
	}
	
	public Semester getById(Integer id){
		return JdbcHelper.queryForObject(jdbcTemplate, Sql.GET_BY_ID, rm, id);
	}
	
	public List<Semester> query(Semester param,Integer has,Integer nextSize) {
		StringBuilder sql = new StringBuilder(Sql.GET);
		if (StringUtils.isNotBlank(param.getName())) {
			sql.append(" and name=:name");
		}
		if(param.getSchoolId()!=null){
			sql.append(" and school_id=:schoolId");
		}
		if(has!=null&&nextSize!=null)
			sql.append(" limit ").append(has).append(",").append(nextSize);
		return namedParameterJdbcTemplate.query(sql.toString(), JdbcHelper.getSqlParameterSource(param), rm);
	}
	
	public void update(Semester bean) {
		namedParameterJdbcTemplate.update(Sql.UPDATE, JdbcHelper.getSqlParameterSource(bean));
	}
	
	public void delete(Integer id) {
		jdbcTemplate.update(Sql.DEL_BY_ID, id);
	}
	
	private class Sql {
		public static final String GET = "select * from semester where 1=1";
		public static final String GET_BY_ID = "select * from semester where id=?";
		public static final String ADD = "insert into semester (name,school_id,start_date,end_date,create_date,update_date) values (:name,:schoolId,:startDate,:endDate,now(),now())";
		public static final String DEL_BY_ID = "delete from semester where id=?";
		public static final String UPDATE = "update semester set name=ifnull(:name,name),school_id=ifnull(:schoolId,school_id),start_date=ifnull(:startDate,start_date),end_date=ifnull(:endDate,end_date) where id=:id";
	}
	
	
	
}
